﻿using EvaluationSystem.Dto;
using EvaluationSystem.Models;
using EvaluationSystem.Models.Views;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace EvaluationSystem
{
    public class MyDbContext : DbContext
    {
        #region Tables

        public DbSet<Student> Student { get; set; }
        public DbSet<Teacher> Teacher { get; set; }
        public DbSet<Administrator> Administrator { get; set; }
        public DbSet<Course> Course { get; set; }
        public DbSet<Claim> Claim { get; set; }
        public DbSet<Comment> Comment { get; set; }
        public DbSet<People> People { get; set; }
        public DbSet<Notify> Notifies { get; set; }
        public DbSet<StudentEvalCourse> StudentCourse { get; set; }
        public DbSet<TeacherWithCourse> TeacherCourse { get; set; }

        #endregion

        #region Views

        public DbSet<Eval_Course> Eval_Course { get; set; }
        public DbSet<Excel_View> Excel_View { get; set; }

        #endregion

        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Student>(entity =>
            {
                entity.ToTable("student");
                entity.Property(t => t.id).HasColumnName("id").IsRequired();
                entity.HasKey(t => t.id);
            });

            modelBuilder.Entity<Teacher>(entity =>
            {
                entity.ToTable("teacher");
                entity.Property(t => t.id).HasColumnName("id").IsRequired();
                entity.HasKey(t => t.id);
            });

            modelBuilder.Entity<Administrator>(entity =>
            {
                entity.ToTable("administrator");
                entity.Property(t => t.id).HasColumnName("id").IsRequired();
                entity.HasKey(t => t.id);
            });


            modelBuilder.Entity<Comment>(entity =>
            {
                entity.ToTable("comment");
                entity.Property(t => t.CommentId).HasColumnName("id").IsRequired();
                entity.Property(t => t.Content).HasColumnName("content");
                entity.Property(t => t.ScId).HasColumnName("scid");
                entity.Property(t => t.PublisherId).HasColumnName("pid");
                entity.Property(t => t.ReplierId).HasColumnName("rid");
                entity.Property(t => t.CreatedAt).HasColumnName("createdAt");

                entity.HasKey(t => t.CommentId);
                entity.HasOne(t => t.StudentEvalCourse).WithMany().HasForeignKey(t => t.ScId);
                entity.HasOne(t => t.Publisher).WithMany().HasForeignKey(t => t.PublisherId);
                entity.HasOne(t => t.Replier).WithMany().HasForeignKey(t => t.ReplierId);
            });

            modelBuilder.Entity<Excel_View>(entity =>
               {
                   entity.ToView("excel_view");
                   entity.HasKey(t => new
                   {
                       id = t.Id,
                       sid = t.SId,
                       tid = t.TId
                   });
               });

            modelBuilder.Entity<Eval_Course>(entity =>
            {
                entity.ToView("eval_course");
                entity.Property(t => t.ScId).HasColumnName("scid").IsRequired();
                entity.Property(t => t.Evals).HasColumnName("evals");

                entity.HasKey(t => t.ScId);
            });

            modelBuilder.Entity<People>(entity =>
            {
                entity.ToTable("people");
                entity.Property(t => t.PeopleId).HasColumnName("id").IsRequired();
                entity.Property(t => t.Number).HasColumnName("number");
                entity.Property(t => t.Career).HasColumnName("career")
                    .HasConversion(new ValueConverter<Identity, string>(
                    v => v.ToString().ToLower(),
                    v => (Identity)Enum.Parse(typeof(Identity), v.ToUpper())));
                entity.Property(t => t.Name).HasColumnName("name");
                entity.Property(t => t.Password).HasColumnName("password");
                entity.Property(t => t.CreatedAt).HasColumnName("createdAt");

                entity.HasKey(t => t.PeopleId);
                entity.HasMany(t => t.TeacherWithCourses).WithOne(t => t.Teacher);
                entity.HasMany(t => t.StudentEvalCourses).WithOne(t => t.Student);
                entity.HasMany(t => t.Notifies).WithOne(t => t.Leader);
            });

            modelBuilder.Entity<Course>(entity =>
            {
                entity.ToTable("course");
                entity.Property(t => t.CourseId).HasColumnName("id").IsRequired();

                entity.HasKey(t => t.CourseId);
                entity.HasMany(t => t.TeacherWithCourses).WithOne(t => t.Course).HasForeignKey(t => t.CourseId);
            });

            modelBuilder.Entity<Notify>(entity =>
            {
                entity.ToTable("notify");
                entity.Property(t => t.NotifyId).HasColumnName("id").IsRequired();
                entity.Property(t => t.Title).HasColumnName("title").IsRequired();
                entity.Property(t => t.Content).HasColumnName("content").IsRequired();
                entity.Property(t => t.CreatedAt).HasColumnName("createdAt");

                entity.HasKey(t => t.NotifyId);
                entity.HasOne(t => t.Leader).WithMany(t => t.Notifies).HasForeignKey(t => t.LeaderId);
            });

            modelBuilder.Entity<TeacherWithCourse>(entity =>
            {
                entity.ToTable("teacher_course");
                entity.Property(t => t.TcId).HasColumnName("id").IsRequired();
                entity.Property(t => t.CourseId).HasColumnName("cid");
                entity.Property(t => t.TeacherId).HasColumnName("tid");

                entity.HasKey(t => t.TcId);
                entity.HasOne(t => t.Course).WithMany(t => t.TeacherWithCourses).HasForeignKey(t => t.CourseId);
                entity.HasOne(t => t.Teacher).WithMany(t => t.TeacherWithCourses).HasForeignKey(t => t.TeacherId);
            });

            modelBuilder.Entity<StudentEvalCourse>(entity =>
            {
                entity.ToTable("student_course");
                entity.Property(t => t.ScId).HasColumnName("id").IsRequired();
                entity.Property(t => t.StudentId).HasColumnName("sid");
                entity.Property(t => t.TeacherCourserId).HasColumnName("tcid");
                entity.Property(t => t.Evals).HasColumnName("evals");
                entity.Property(t => t.Score).HasColumnName("score");

                entity.HasKey(t => t.ScId);
                entity.HasOne(sc => sc.Student)
                    .WithMany(t => t.StudentEvalCourses)
                    .HasForeignKey(t => t.StudentId);
                entity.HasOne(sc => sc.TeacherWithCourses)
                    .WithMany(t => t.StudentEvalCourses)
                    .HasForeignKey(t => t.TeacherCourserId);

            });

            base.OnModelCreating(modelBuilder);
        }
    }
}
